﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DAO
{
    public class ChiTietDonDatHangDAO : DataAccess
    {
        public ChiTietDonDatHang CreateObj(DataRow row)
        {
            ChiTietDonDatHang obj = new ChiTietDonDatHang();
            obj.MaCTDDH = Utils.ToStr(row["MaCTDDH"]);
            obj.MaDDH = Utils.ToStr(row["MaDDH"]);
            obj.MaSP = Utils.ToStr(row["ID"]);
            obj.SoLuong = Utils.ToInt(row["SoLuong"]);
            obj.DonGia = Utils.ToFloat(row["DonGia"]);
            obj.ThanhTien = Utils.ToFloat(row["ThanhTien"]);
            obj.CV = Utils.ToInt(row["CV"]);
            return obj;
        }
        public List<ChiTietDonDatHang> GetListFromDDH(DonDatHang obj)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.AppendLine(string.Format("SELECT ctt.MaCTDDH, '{0}' AS \"MaDDH\", sp.ID, sp.MaSP, ctt.SoLuong, CASE WHEN ctt.DonGia IS NULL THEN sp.DonGia ELSE ctt.DonGia END AS \"DonGia\", ctt.ThanhTien, ctt.CV", obj.MaDDH));
            SQL.AppendLine("FROM SanPham sp");
            SQL.AppendLine("LEFT JOIN");
            SQL.AppendLine("(SELECT *");
            SQL.AppendLine("FROM ChiTietDonDatHang ct");
            SQL.AppendLine("WHERE ct.MaDDH = ?) ctt");
            SQL.AppendLine("ON sp.ID = ctt.MaSP");
            SQL.AppendLine("ORDER BY sp.CV DESC");
            DataTable tb = GetData(SQL.ToString(), "@MaDDH", obj.MaDDH, OleDbType.VarChar);
            List<ChiTietDonDatHang> Lst = new List<ChiTietDonDatHang>();
            for (int i = 0; i < tb.Rows.Count; i++)
                Lst.Add(CreateObj(tb.Rows[i]));
            return Lst;
        }
        public List<ChiTietDonDatHang> GetListEmty()
        {
            StringBuilder SQL = new StringBuilder();
            SQL.AppendLine("SELECT NULL AS MaCTDDH, NULL AS  \"MaDDH\",sp.ID, sp.MaSP, NULL AS \"SoLuong\", sp.DonGia, NULL AS \"ThanhTien\", sp.CV");
            SQL.AppendLine("FROM SanPham sp");
            SQL.AppendLine("ORDER BY sp.CV DESC");
            DataTable tb = GetData(SQL.ToString());
            List<ChiTietDonDatHang> Lst = new List<ChiTietDonDatHang>();
            for (int i = 0; i < tb.Rows.Count; i++)
                Lst.Add(CreateObj(tb.Rows[i]));
            return Lst;
        }
        public bool Insert(ChiTietDonDatHang obj, OleDbConnection Con, OleDbTransaction tran)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.AppendLine("DECLARE @ID VARCHAR(50) = NEWID()");
            SQL.AppendLine("INSERT INTO ChiTietDonDatHang(MaCTDDH, MaDDH, MaSP, SoLuong, DonGia, ThanhTien, CV)");
            SQL.AppendLine("VALUES(@ID, ?, ?, ?, ?, ?, ?)");
            SQL.AppendLine("SELECT @ID");
            OleDbCommand cmd = new OleDbCommand(SQL.ToString(), Con, tran);
            cmd.Parameters.Add("@MaDDH", OleDbType.VarChar).Value = obj.MaDDH;
            cmd.Parameters.Add("@MaSP", OleDbType.VarChar).Value = obj.MaSP;
            cmd.Parameters.Add("@SoLuong", OleDbType.Integer).Value = obj.SoLuong;
            cmd.Parameters.Add("@DonGia", OleDbType.Double).Value = obj.DonGia;
            cmd.Parameters.Add("@ThanhTien", OleDbType.Double).Value = obj.ThanhTien;
            cmd.Parameters.Add("@CV", OleDbType.Integer).Value = obj.CV;
            obj.MaCTDDH = Utils.ToStr(cmd.ExecuteScalar());
            if (string.IsNullOrEmpty(obj.MaCTDDH))
                return false;
            else
                return true;
        }
    }
}
